import os

from django.db.models import F

if __name__ == '__main__':
    os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'LearnProject2.settings')
    import django

    django.setup()
    import json

    from app01 import models
    import xlrd2 as xlrd


    # 先把数据转为json格式
    def xlsx_data_json():
        # 读取路径为app01/customer_data_floder/customer_data.xlsx的文件将数据转为json格式
        # 打开文件
        workbook = xlrd.open_workbook(r'customer_data.xlsx')
        # 获取sheet
        sheet = workbook.sheet_by_index(0)
        # 获取行数
        rows = sheet.nrows
        # 获取列数
        cols = sheet.ncols

        # 获取所有表头
        title = sheet.row_values(0)
        # 获取前10行数据
        data = []
        for i in range(1, rows):
            print('读取第{}行'.format(i))
            data.append(sheet.row_values(i))
        # 将表头和数据组合成字典
        data_json = []
        for i in range(len(data)):
            data_json.append(dict(zip(title, data[i])))
        # 将字典转为json格式
        data_json = json.dumps(data_json, ensure_ascii=False)
        # 将json格式写入文件
        with open('customer_data.json', 'w', encoding='utf-8') as f:
            f.write(data_json)
        # print(data_json)


    def clean_data(i):
        deposit_amount_choice = [
            (1, "低于30万"),
            (2, "30万——50万"),
            (3, "50万-200万"),
            (4, "超过200万"),
        ]
        if i['deposit_amount']<300000:
            i['deposit_amount']=1
        elif 300000<=i['deposit_amount']<500000:
            i['deposit_amount']=2
        elif 500000<=i['deposit_amount']<2000000:
            i['deposit_amount']=3
        elif i['deposit_amount']>=2000000:
            i['deposit_amount']=4
        # 如果i['gender']为男则将其转为1，否则转为2
        i['gender'] = 1 if i['gender'] == '男' else 2
        i['customer_relationship_type'] = 1 if i['customer_relationship_type'] == '主投顾关系' else 2
        #  Cannot assign "1": "CustomerList.customer_relationship_state" must be a "RelationshipState" instance.
        # i['customer_relationship_state'] = 1 if i['customer_relationship_state'] == '有效' else 2

        data_list = []

        i['financial_product'] = 1 if i['financial_product'] == '是' else 0
        # 将i['financial_product']字段名加一个’_count‘和值添加到data_list中
        data_list.append(('financial_product_count', i['financial_product']))
        i['fund_products'] = 1 if i['fund_products'] == '是' else 0
        data_list.append(('fund_products_count', i['fund_products']))
        i['investment_assets'] = 1 if i['investment_assets'] == '是' else 0
        data_list.append(('investment_assets_count', i['investment_assets']))
        i['add_weChat'] = 1 if i['add_weChat'] == '是' else 0
        data_list.append(('add_weChat_count', i['add_weChat']))
        i['contract_stock'] = 1 if i['contract_stock'] == '是' else 0
        data_list.append(('contract_stock_count', i['contract_stock']))
        i['yield_enhanced_products'] = 1 if i['yield_enhanced_products'] == '是' else 0
        data_list.append(('yield_enhanced_products_count', i['yield_enhanced_products']))
        i['equity_public_offering'] = 1 if i['equity_public_offering'] == '是' else 0
        data_list.append(('equity_public_offering_count', i['equity_public_offering']))
        i['small_t_one_assets'] = 1 if i['small_t_one_assets'] == '是' else 0
        data_list.append(('small_t_one_assets_count', i['small_t_one_assets']))
        i['stock_financing'] = 1 if i['stock_financing'] == '是' else 0
        data_list.append(('stock_financing_count', i['stock_financing']))
        i['more_t_one_assets'] = 1 if i['more_t_one_assets'] == '是' else 0
        data_list.append(('more_t_one_assets_count', i['more_t_one_assets']))
        i['Private_equity_products'] = 1 if i['Private_equity_products'] == '是' else 0
        data_list.append(('Private_equity_products_count', i['Private_equity_products']))
        i['configure_China'] = 1 if i['configure_China'] == '是' else 0
        data_list.append(('configure_China_count', i['configure_China']))
        if i['staff_id']:
            i['staff_id'] = models.StaffInfo.objects.get(name=i['staff_id']).id
            # 遍历data_list，如果值为1，则将StaffInfo表中相应的字段值加1，否则不变
            for j in data_list:
                if j[1] == 1:
                    models.StaffInfo.objects.filter(id=i['staff_id']).update(**{j[0]: F(j[0]) + 1})


            #修改客户数量+1
            models.StaffInfo.objects.filter(id=i['staff_id']).update(customer_count=F('customer_count') + 1)
        else:
            i['staff_id'] = None
        #修改i['staff']的字段名
        print('修改i[\'staff\']的字段名',i['staff_id'])
        i['staff'] = i['staff_id']



    # xlsx_data_json()

    def json_data_db():
        # 读取路径为app01/customer_data_floder/customer_data.json的文件将数据写入数据库
        with open('customer_data.json', 'r', encoding='utf-8') as f:
            data = json.load(f)
            for i in data:
                # 处理数据
                # 将id字段移除
                i.pop('id')
                i.pop('customer_relationship_state')

                clean_data(i)
                i.pop('staff')
                i.pop('')
                print(i)
                #
                models.CustomerList.objects.create(**i)
                print('写入第{}条数据'.format(data.index(i) + 1))


    json_data_db()
